package com.ecsolutions.dao.asset;

import com.ecsolutions.entity.asset.DepositCurrencyEntity;
import com.ecsolutions.entity.asset.DepositNatureEntity;
import com.ecsolutions.entity.asset.DepositPercentEntity;
import com.ecsolutions.entity.asset.DepositTypeEntity;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.ResultType;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created by Administrator on 2017/5/10.
 */
@Repository
public interface DepositDao {
    @Select("select currency, sum(account_balance) balance_sum from \n" +
            "(select * from DEPOSIT_ACCOUNT_INFO where trim(Customer_Code) = trim(#{custcode}))\n" +
            "group by currency\n")
    @ResultType(DepositCurrencyEntity.class)
    List<DepositCurrencyEntity> getBalanceOfEachCurrency(@Param("custcode") String customer_code);

    @Select("select account_type,sum(account_balance) balance_sum from \n" +
            "(select * from deposit_ACCOUNT_INFO where trim(Customer_Code) = trim(#{custcode}) and currency = #{currency} )\n" +
            "group by account_type")
    @ResultType(DepositCurrencyEntity.class)
    List<DepositCurrencyEntity> getBalanceOfEachAccType(@Param("custcode") String customer_code, @Param("currency") String currency);

    @Select("select currency, sum(account_balance) balance_sum from \n" +
            "(select * from DEPOSIT_ACCOUNT_INFO where trim(Customer_Code) = trim(#{custcode}))\n" +
            "group by currency\n")
    @ResultType(DepositCurrencyEntity.class)
    List<DepositCurrencyEntity> getCurrencies(@Param("custcode") String customer_code);


    @Select("select account_type type,sum(account_balance) balance_sum from \n" +
            "(select * from deposit_ACCOUNT_INFO where trim(Customer_Code) = trim(#{custcode}) and currency = #{currency} )\n" +
            "group by account_type")
    @ResultType(DepositTypeEntity.class)
    List<DepositTypeEntity> getDepositTypes(@Param("custcode") String customer_code, @Param("currency") String currency);

    @Select("Select ACCOUNT_NATURE,currency, sum(account_balance) balance_sum from\n" +
            " (select * from DEPOSIT_ACCOUNT_INFO where trim(Customer_Code) = trim(#{customer_code})) \n" +
            " group by ACCOUNT_NATURE,currency \n" +
            " order by ACCOUNT_NATURE")
    @ResultType(DepositNatureEntity.class)
    List<DepositNatureEntity> getNatures(@Param("customer_code") String customer_code);

    @Select("select ACCOUNT_NATURE, round(sum1 / sum2 * 100, 0) percent\n" +
            "  from (select ACCOUNT_NATURE, sum(account_balance_lcy) sum1\n" +
            "          from (select *\n" +
            "                  from DEPOSIT_ACCOUNT_INFO\n" +
            "                 where trim(Customer_Code) = trim(#{customer_code}))\n" +
            "         group by ACCOUNT_NATURE),\n" +
            "       (select sum(account_balance_lcy) sum2\n" +
            "          from DEPOSIT_ACCOUNT_INFO\n" +
            "         where trim(Customer_Code) = trim(#{customer_code})) \n" +
            " order by ACCOUNT_NATURE")
    @ResultType(DepositPercentEntity.class)
    List<DepositPercentEntity> getPercent(@Param("customer_code") String customer_code);


    @Select("Select currency, sum(account_balance) balance_sum\n" +
            "from (select * from DEPOSIT_ACCOUNT_INFO\n" +
            " where trim(Customer_Code) = trim(#{customer_code}) and trim(ACCOUNT_NATURE) = trim(#{nature}) )\n" +
            "group by currency\n" +
            "order by currency")
    @ResultType(DepositCurrencyEntity.class)
    List<DepositCurrencyEntity> getNatureCurrencies(@Param("customer_code") String customer_code, @Param("nature") String nature);

    @Select("select ACCOUNT_NATURE, round(sum1 / sum2, 2) percent\n" +
            "  from (select ACCOUNT_NATURE, sum(account_balance_lcy) sum1\n" +
            "          from (select *\n" +
            "                  from DEPOSIT_ACCOUNT_INFO\n" +
            "                 where trim(Customer_Code) = trim(#{customer_code}))\n" +
            "         group by ACCOUNT_NATURE),\n" +
            "       (select sum(account_balance_lcy) sum2\n" +
            "          from DEPOSIT_ACCOUNT_INFO\n" +
            "         where trim(Customer_Code) = trim(#{customer_code})) \n" +
            " order by ACCOUNT_NATURE")
    @ResultType(DepositNatureEntity.class)
    List<DepositNatureEntity> getNaturePercent(@Param("customer_code") String customer_code);

    @Select("select ACCOUNT_TYPE,DP_ACCOUNT_NO\n" +
            "from DEPOSIT_ACCOUNT_INFO \n" +
            "where trim(Customer_Code) = trim(#{customer_code}) and trim(ACCOUNT_NATURE) = trim(#{nature})\n" +
            "order by ACCOUNT_TYPE,DP_ACCOUNT_NO")
    @ResultType(DepositTypeEntity.class)
    List<DepositTypeEntity> getNatureAccounts(@Param("customer_code") String customer_code, @Param("nature") String nature);

    @Select("select CURRENCY,ACCOUNT_BALANCE\n" +
            "from DEPOSIT_ACCOUNT_INFO \n" +
            "where trim(Customer_Code) = trim(#{customer_code}) and trim(ACCOUNT_NATURE) = trim(#{nature})\n" +
            "and trim(DP_ACCOUNT_NO) = trim(#{account})\n" +
            "order by CURRENCY")
    List<DepositCurrencyEntity> getAccountCurrencies(@Param("customer_code") String customer_code, @Param("nature") String nature, @Param("account") String account);
}
